package sf.dsl.example;

/**
 * Common SQL expressions
 * @see com.querydsl.sql.SQLExpressions
 */
public final class SQLExpressions {

    /**
     * Create a new detached SQLQuery instance with zero as the projection
     * @return select(0)
     */
    public static Example selectZero() {
        return new Example().select(QPI.fixColumn("0"));
    }

    /**
     * Create a new detached SQLQuery instance with one as the projection
     * @return select(1)
     */
    public static Example selectOne() {
        return new Example().select(QPI.fixColumn("1"));
    }

    private static final WindowOver cumeDist = new WindowOver(new SimpleField("cume_dist()"));

    private static final WindowOver rank = new WindowOver(new SimpleField("rank()"));

    private static final WindowOver denseRank = new WindowOver(new SimpleField("dense_rank()"));

    private static final WindowOver percentRank = new WindowOver(new SimpleField("percent_rank()"));

    private static final WindowOver rowNumber = new WindowOver(new SimpleField("row_number()"));

    private static SimpleField[] convertToExpressions(Object... args) {
        SimpleField[] exprs = new SimpleField[args.length];
        for (int i = 0; i < args.length; i++) {
            if (args[i] instanceof SimpleField) {
                exprs[i] = (SimpleField) args[i];
            } else {
                exprs[i] = new SimpleField(String.valueOf(args[i]));
            }
        }
        return exprs;
    }

    /**
     * Start a window function expression
     * @param expr expression
     * @return sum(expr)
     */
    public static WindowOver sum(SimpleField expr) {
        return new WindowOver(QPI.sum(expr));
    }

    /**
     * Start a window function expression
     * @return count()
     */
    public static WindowOver count() {
        return new WindowOver(QPI.count());
    }

    /**
     * Start a window function expression
     * @param expr expression
     * @return count(expr)
     */
    public static WindowOver count(SimpleField expr) {
        return new WindowOver(QPI.count(expr));
    }

    /**
     * Start a window function expression
     * @param expr expression
     * @return count(distinct expr)
     */
    public static WindowOver countDistinct(SimpleField expr) {
        return new WindowOver(QPI.funcO("count(distinct ", ")", expr));
    }

    /**
     * Start a window function expression
     * @param expr expression
     * @return avg(expr)
     */
    public static WindowOver avg(SimpleField expr) {
        return new WindowOver(QPI.avg(expr));
    }

    /**
     * Start a window function expression
     * @param expr expression
     * @return min(expr)
     */
    public static WindowOver min(SimpleField expr) {
        return new WindowOver(QPI.min(expr));
    }

    /**
     * Start a window function expression
     * @param expr expression
     * @return max(expr)
     */
    public static WindowOver max(SimpleField expr) {
        return new WindowOver(QPI.max(expr));
    }

    /**
     * expr evaluated at the row that is one row after the current row within the partition;
     * @param expr expression
     * @return lead(expr)
     */
    public static WindowOver lead(SimpleField expr) {
        return new WindowOver(QPI.func("lead", expr));
    }

    /**
     * expr evaluated at the row that is one row before the current row within the partition
     * @param expr expression
     * @return lag(expr)
     */
    public static WindowOver lag(SimpleField expr) {
        return new WindowOver(QPI.func("lag", expr));
    }

    /**
     * LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates
     * the values of the measure column.
     * @param expr      measure column
     * @param delimiter delimiter
     * @return listagg(expr, delimiter)
     */
    public static WithinGroup listagg(SimpleField expr, String delimiter) {
        return new WithinGroup(QPI.func("listagg", expr, QPI.fixColumn("'" + delimiter + "'")));
    }

    /**
     * NTH_VALUE returns the expr value of the nth row in the window defined by the analytic clause.
     * The returned value has the data type of the expr
     * @param expr measure expression
     * @param n    one based row index
     * @return nth_value(expr, n)
     */
    public static WindowOver nthValue(SimpleField expr, Number n) {
        return new WindowOver(QPI.func("nth_value", expr, QPI.fixColumn(String.valueOf(n))));
    }

    /**
     * divides an ordered data set into a number of buckets indicated by expr and assigns the
     * appropriate bucket number to each row
     * @param num bucket size
     * @return ntile(num)
     */
    @SuppressWarnings("unchecked")
    public static WindowOver ntile(Number num) {
        return new WindowOver(QPI.func("ntile", QPI.fixColumn(num.toString())));
    }

    /**
     * rank of the current row with gaps; same as row_number of its first peer
     * @return rank()
     */
    public static WindowOver rank() {
        return rank;
    }

    /**
     * As an aggregate function, RANK calculates the rank of a hypothetical row identified by the
     * arguments of the function with respect to a given sort specification. The arguments of the
     * function must all evaluate to constant expressions within each aggregate group, because they
     * identify a single row within each group. The constant argument expressions and the expressions
     * in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments
     * must be the same and their types must be compatible.
     * @param args arguments
     * @return rank(args)
     */
    @SuppressWarnings("unchecked")
    public static WithinGroup rank(Object... args) {
        return rank(convertToExpressions(args));
    }

    /**
     * As an aggregate function, RANK calculates the rank of a hypothetical row identified by the
     * arguments of the function with respect to a given sort specification. The arguments of the
     * function must all evaluate to constant expressions within each aggregate group, because they
     * identify a single row within each group. The constant argument expressions and the expressions
     * in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments
     * must be the same and their types must be compatible.
     * @param args arguments
     * @return rank(args)
     */
    public static WithinGroup rank(SimpleField... args) {
        return new WithinGroup(QPI.func("rank", args));
    }

    /**
     * rank of the current row without gaps; this function counts peer groups
     * @return dense_rank()
     */
    public static WindowOver denseRank() {
        return denseRank;
    }

    /**
     * As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical row identified
     * by the arguments of the function with respect to a given sort specification. The arguments of
     * the function must all evaluate to constant expressions within each aggregate group, because they
     * identify a single row within each group. The constant argument expressions and the expressions
     * in the order_by_clause of the aggregate match by position. Therefore, the number of arguments
     * must be the same and types must be compatible.
     * @param args arguments
     * @return dense_rank(args)
     */
    @SuppressWarnings("unchecked")
    public static WithinGroup denseRank(Object... args) {
        return denseRank(convertToExpressions(args));
    }

    /**
     * As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical row identified
     * by the arguments of the function with respect to a given sort specification. The arguments of
     * the function must all evaluate to constant expressions within each aggregate group, because they
     * identify a single row within each group. The constant argument expressions and the expressions
     * in the order_by_clause of the aggregate match by position. Therefore, the number of arguments
     * must be the same and types must be compatible.
     * @param args arguments
     * @return dense_rank(args)
     */
    public static WithinGroup denseRank(SimpleField... args) {
        return new WithinGroup(QPI.func("dense_rank", args));
    }

    /**
     * As an analytic function, for a row r, PERCENT_RANK calculates the rank of r minus 1, divided by
     * 1 less than the number of rows being evaluated (the entire query result set or a partition).
     * @return percent_rank()
     */
    public static WindowOver percentRank() {
        return percentRank;
    }

    /**
     * As an aggregate function, PERCENT_RANK calculates, for a hypothetical row r identified by the
     * arguments of the function and a corresponding sort specification, the rank of row r minus 1
     * divided by the number of rows in the aggregate group. This calculation is made as if the
     * hypothetical row r were inserted into the group of rows over which Oracle Database is to
     * aggregate. The arguments of the function identify a single hypothetical row within each aggregate
     * group. Therefore, they must all evaluate to constant expressions within each aggregate group.
     * The constant argument expressions and the expressions in the ORDER BY clause of the aggregate
     * match by position. Therefore the number of arguments must be the same and their types must be
     * compatible.
     * @param args arguments
     * @return percent_rank(args)
     */
    @SuppressWarnings("unchecked")
    public static WithinGroup percentRank(Object... args) {
        return percentRank(convertToExpressions(args));
    }

    /**
     * As an aggregate function, PERCENT_RANK calculates, for a hypothetical row r identified by the
     * arguments of the function and a corresponding sort specification, the rank of row r minus 1
     * divided by the number of rows in the aggregate group. This calculation is made as if the
     * hypothetical row r were inserted into the group of rows over which Oracle Database is to aggregate.
     * The arguments of the function identify a single hypothetical row within each aggregate group.
     * Therefore, they must all evaluate to constant expressions within each aggregate group. The
     * constant argument expressions and the expressions in the ORDER BY clause of the aggregate match
     * by position. Therefore the number of arguments must be the same and their types must be compatible.
     * @param args arguments
     * @return percent_rank(args)
     */
    public static WithinGroup percentRank(SimpleField... args) {
        return new WithinGroup(QPI.func("percent_rank", args));
    }

    /**
     * Calculates a percentile based on a continuous distribution of the column value
     * @param arg argument
     * @return percentile_cont(arg)
     */
    public static WithinGroup percentileCont(Number arg) {
        if (arg.doubleValue() < 0.0 || arg.doubleValue() > 1.0) {
            throw new IllegalArgumentException("The percentile value should be a number between 0 and 1");
        }
        return percentileCont(QPI.fixColumn(String.valueOf(arg)));
    }

    /**
     * Calculates a percentile based on a continuous distribution of the column value
     * @param arg argument
     * @return percentile_cont(arg)
     */
    public static <T extends Number> WithinGroup percentileCont(SimpleField arg) {
        return new WithinGroup(QPI.func("percentile_cont", arg));
    }

    /**
     * PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model.
     * It takes a percentile value and a sort specification and returns an element from the set.
     * Nulls are ignored in the calculation.
     *
     * <p>This function takes as an argument any numeric datatype or any nonnumeric datatype that can be
     * implicitly converted to a numeric datatype. The function returns the same datatype as the numeric
     * datatype of the argument.</p>
     * @param arg argument
     * @return percentile_disc(arg)
     */
    public static WithinGroup percentileDisc(Number arg) {
        if (arg.doubleValue() < 0.0 || arg.doubleValue() > 1.0) {
            throw new IllegalArgumentException("The percentile value should be a number between 0 and 1");
        }
        return percentileDisc(QPI.fixColumn(String.valueOf(arg)));
    }

    /**
     * PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model.
     * It takes a percentile value and a sort specification and returns an element from the set.
     * Nulls are ignored in the calculation.
     *
     * <p>This function takes as an argument any numeric datatype or any nonnumeric datatype that can be
     * implicitly converted to a numeric datatype. The function returns the same datatype as the numeric
     * datatype of the argument.</p>
     * @param arg argument
     * @return percentile_disc(arg)
     */
    public static <T extends Number> WithinGroup percentileDisc(/*Number*/SimpleField arg) {
        return new WithinGroup(QPI.func("percentile_disc", arg));
    }

    /**
     * REGR_SLOPE returns the slope of the line
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_slope(arg1, arg2)
     */
    public static WindowOver regrSlope(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_slope", arg1, arg2));
    }

    /**
     * REGR_INTERCEPT returns the y-intercept of the regression line.
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_intercept(arg1, arg2)
     */
    public static WindowOver regrIntercept(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_intercept", arg1, arg2));
    }

    /**
     * REGR_COUNT returns an integer that is the number of non-null number pairs used to fit the regression line.
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_count(arg1, arg2)
     */
    public static WindowOver regrCount(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_count", arg1, arg2));
    }

    /**
     * REGR_R2 returns the coefficient of determination (also called R-squared or goodness of fit) for the regression.
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_r2(arg1, arg2)
     */
    public static WindowOver regrR2(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_r2", arg1, arg2));
    }

    /**
     * REGR_AVGX evaluates the average of the independent variable (arg2) of the regression line.
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_avgx(arg1, arg2)
     */
    public static WindowOver regrAvgx(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_avgx", arg1, arg2));
    }

    /**
     * REGR_AVGY evaluates the average of the dependent variable (arg1) of the regression line.
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_avgy(arg1, arg2)
     */
    public static WindowOver regrAvgy(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_avgy", arg1, arg2));
    }

    /**
     * REGR_SXX makes the following computation after the elimination of null (arg1, arg2) pairs:
     *
     * <p>{@code REGR_COUNT(arg1, arg2) * VAR_POP(arg2)}</p>
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_sxx(arg1, arg2)
     */
    public static WindowOver regrSxx(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_sxx", arg1, arg2));
    }

    /**
     * REGR_SYY makes the following computation after the elimination of null (arg1, arg2) pairs:
     *
     * <p>{@code REGR_COUNT(arg1, arg2) * VAR_POP(arg1)}</p>
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_syy(arg1, arg2)
     */
    public static WindowOver regrSyy(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_syy", arg1, arg2));
    }

    /**
     * REGR_SXY makes the following computation after the elimination of null (arg1, arg2) pairs:
     *
     * <p>REGR_COUNT(arg1, arg2) * COVAR_POP(arg1, arg2)</p>
     * @param arg1 first arg
     * @param arg2 second arg
     * @return regr_sxy(arg1, arg2)
     */
    public static WindowOver regrSxy(SimpleField arg1, SimpleField arg2) {//Number
        return new WindowOver(QPI.func("regr_sxy", arg1, arg2));
    }

    /**
     * CUME_DIST calculates the cumulative distribution of a value in a group of values.
     * @return cume_dist()
     */
    public static WindowOver cumeDist() {
        return cumeDist;
    }

    /**
     * As an aggregate function, CUME_DIST calculates, for a hypothetical row r identified by the
     * arguments of the function and a corresponding sort specification, the relative position of row
     * r among the rows in the aggregation group. Oracle makes this calculation as if the hypothetical
     * row r were inserted into the group of rows to be aggregated over. The arguments of the function
     * identify a single hypothetical row within each aggregate group. Therefore, they must all
     * evaluate to constant expressions within each aggregate group. The constant argument expressions
     * and the expressions in the ORDER BY clause of the aggregate match by position. Therefore,
     * the number of arguments must be the same and their types must be compatible.
     * @param args arguments
     * @return cume_dist(args)
     */
    @SuppressWarnings("unchecked")
    public static WithinGroup cumeDist(Object... args) {
        return cumeDist(convertToExpressions(args));
    }

    /**
     * As an aggregate function, CUME_DIST calculates, for a hypothetical row r identified by the
     * arguments of the function and a corresponding sort specification, the relative position of row
     * r among the rows in the aggregation group. Oracle makes this calculation as if the hypothetical
     * row r were inserted into the group of rows to be aggregated over. The arguments of the function
     * identify a single hypothetical row within each aggregate group. Therefore, they must all
     * evaluate to constant expressions within each aggregate group. The constant argument expressions
     * and the expressions in the ORDER BY clause of the aggregate match by position. Therefore,
     * the number of arguments must be the same and their types must be compatible.
     * @param args arguments
     * @return cume_dist(args)
     */
    public static WithinGroup cumeDist(SimpleField... args) {
        return new WithinGroup(QPI.func("cume_dist", args));
    }

    /**
     * CORR returns the coefficient of correlation of a set of number pairs.
     * @param expr1 first arg
     * @param expr2 second arg
     * @return corr(expr1, expr2)
     */
    public static WindowOver corr(SimpleField expr1, SimpleField expr2) {
        return new WindowOver(QPI.func("corr", expr1, expr2));
    }

    /**
     * CORR returns the coefficient of correlation of a set of number pairs.
     * @param expr1 first arg
     * @param expr2 second arg
     * @return covar_pop(expr1, expr2)
     */
    public static WindowOver covarPop(SimpleField expr1, SimpleField expr2) {//Number
        return new WindowOver(QPI.func("covar_pop", expr1, expr2));
    }

    /**
     * CORR returns the coefficient of correlation of a set of number pairs.
     * @param expr1 first arg
     * @param expr2 second arg
     * @return covar_samp(expr1, expr2)
     */
    public static WindowOver covarSamp(SimpleField expr1, SimpleField expr2) {//Number
        return new WindowOver(QPI.func("covar_samp", expr1, expr2));
    }

    /**
     * computes the ratio of a value to the sum of a set of values. If expr evaluates to null,
     * then the ratio-to-report value also evaluates to null.
     * @return ratio_to_report(expr)
     */
    public static WindowOver ratioToReport(SimpleField expr) {
        return new WindowOver(QPI.func("ratio_to_report", expr));
    }

    /**
     * number of the current row within its partition, counting from 1
     * @return row_number()
     */
    public static WindowOver rowNumber() {
        return rowNumber;
    }

    /**
     * returns the sample standard deviation of expr, a set of numbers.
     * @param expr argument
     * @return stddev(expr)
     */
    public static <T extends Number> WindowOver stddev(SimpleField expr) {//Number
        return new WindowOver(QPI.func("stddev", expr));
    }

    /**
     * returns the sample standard deviation of expr, a set of numbers.
     * @param expr argument
     * @return stddev(distinct expr)
     */
    public static <T extends Number> WindowOver stddevDistinct(SimpleField expr) {//Number
        return new WindowOver(QPI.funcO("stddev(distinct ", ")", expr));
    }

    /**
     * returns the population standard deviation and returns the square root of the population variance.
     * @param expr argument
     * @return stddev_pop(expr)
     */
    public static <T extends Number> WindowOver stddevPop(SimpleField expr) {//Number
        return new WindowOver(QPI.func("stddev_pop", expr));
    }

    /**
     * returns the cumulative sample standard deviation and returns the square root of the sample variance.
     * @param expr argument
     * @return stddev_samp(expr)
     */
    public static <T extends Number> WindowOver stddevSamp(SimpleField expr) {//Number
        return new WindowOver(QPI.func("stddev_samp", expr));
    }

    /**
     * returns the variance of expr
     * @param expr argument
     * @return variance(expr)
     */
    public static <T extends Number> WindowOver variance(SimpleField expr) {//Number
        return new WindowOver(QPI.func("variance", expr));
    }

    /**
     * returns the population variance of a set of numbers after discarding the nulls in this set.
     * @param expr argument
     * @return var_pop(expr)
     */
    public static <T extends Number> WindowOver varPop(SimpleField expr) {//Number
        return new WindowOver(QPI.func("var_pop", expr));
    }

    /**
     * returns the sample variance of a set of numbers after discarding the nulls in this set.
     * @param expr argument
     * @return var_samp(expr)
     */
    public static <T extends Number> WindowOver varSamp(SimpleField expr) {//Number
        return new WindowOver(QPI.func("var_samp", expr));
    }

    /**
     * returns value evaluated at the row that is the first row of the window frame
     * @param expr argument
     * @return first_value(expr)
     */
    public static WindowOver firstValue(SimpleField expr) {
        return new WindowOver(QPI.func("first_value", expr));
    }

    /**
     * returns value evaluated at the row that is the last row of the window frame
     * @param expr argument
     * @return last_value(expr)
     */
    public static WindowOver lastValue(SimpleField expr) {
        return new WindowOver(QPI.func("last_value", expr));
    }


    private SQLExpressions() {
    }

}
